# This file is used to create the user_info_ext table in the database.

'''
-- 借用RFID工具箱信息表
CREATE TABLE IF NOT EXISTS lend_rfid_tool_box_info(
    id SERIAL PRIMARY KEY,
    tool_box_mac VARCHAR(20) NOT NULL,
    status VARCHAR(20) NOT NULL, -- lend:借用  return:归还
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id INTEGER default 1
);
'''

class LendRfidToolBoxInfoTableMethods: # 接收RFID工具箱信息表方法类

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_lend_rfid_tool_box_info(self, tool_box_mac, status, user_id):
        sql = "INSERT INTO lend_rfid_tool_box_info (tool_box_mac, status, user_id) VALUES (%s, %s, %s)"
        params = (tool_box_mac, status, user_id)
        self.cursor.execute(sql, params)
        self.conn.commit()

    def get_lend_rfid_tool_box_info_by_tool_box_mac(self, tool_box_mac):
        sql = "SELECT * FROM lend_rfid_tool_box_info WHERE tool_box_mac = %s"
        params = (tool_box_mac,)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchone()
        return result
    
    # 根据mac获取最新一条借用信息状态
    def get_lend_rfid_tool_box_info_status_by_tool_box_mac(self, tool_box_mac):
        sql = "SELECT status FROM lend_rfid_tool_box_info WHERE tool_box_mac = %s order by create_time desc limit 1"
        params = (tool_box_mac,)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchone()
        return result
    

    def update_lend_rfid_tool_box_info_status(self, tool_box_mac, status):
        sql = "UPDATE lend_rfid_tool_box_info SET status = %s WHERE tool_box_mac = %s"
        params = (status, tool_box_mac)
        self.cursor.execute(sql, params)
        self.conn.commit()

    def delete_lend_rfid_tool_box_info_by_tool_box_mac(self, tool_box_mac):
        sql = "DELETE FROM lend_rfid_tool_box_info WHERE tool_box_mac = %s"
        params = (tool_box_mac,)
        self.cursor.execute(sql, params)
        self.conn.commit()

    def get_lend_rfid_tool_box_info_by_user_id(self, user_id=1, limit_num=10, page_num=1):
        start_num = (page_num - 1) * limit_num
        sql = "SELECT * FROM lend_rfid_tool_box_info WHERE user_id = %s order by create_time desc limit %s offset %s"
        params = (user_id, limit_num, start_num)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result

    

    def get_lend_rfid_tool_box_info_by_status(self, status):
        sql = "SELECT * FROM lend_rfid_tool_box_info WHERE status = %s"
        params = (status,)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result
    

    def get_lend_rfid_tool_box_info_by_status_and_user_id(self, status, user_id):
        sql = "SELECT * FROM lend_rfid_tool_box_info WHERE status = %s AND user_id = %s"
        params = (status, user_id)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result